package com.ming.common.beetl.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ClassUtil;
import cn.hutool.core.util.StrUtil;
import com.ming.common.SelectOption;
import com.ming.common.SelectOptionItem;
import com.ming.common.beetl.cache.CacheSqlManagerUtil;
import com.ming.common.beetl.config.IvyParaExtend;
import com.ming.common.beetl.entity.BaseEntity;
import com.ming.common.beetl.entity.IvyDbSql;
import com.ming.common.beetl.entity.IvyDbSqlItem;
import com.ming.common.beetl.execption.SQLBuildException;
import com.ming.common.beetl.vo.IvyDbSqlVo;
import com.ming.common.liteflow.core.node.SpringBeanUtil;
import org.beetl.sql.core.*;
import org.beetl.sql.core.extend.ParaExtend;
import org.beetl.sql.core.query.LambdaQuery;
import org.beetl.sql.ext.DBInitHelper;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;

public class BeetlSqlUtil {

    private volatile static BeetlSqlUtil instance;
    private BeetlSqlUtil(){ }
    public  static BeetlSqlUtil NEW() {
        if (instance == null){
            synchronized (BeetlSqlUtil.class){
                if (instance == null){
                    instance = new BeetlSqlUtil();
                }
            }
        }
        return instance;
    }

    private SQLManager sqlManager;
    private Class<?> clazz;
    private String tableName;
    private IvyDbSqlVo ivyDbSql;
    private String sqlFile;//  db/schema.sql

    public SQLManager getDefaultSQLManager(){
        return SpringBeanUtil.getBean("sqlManager");
    }

    public BeetlSqlUtil sqlManagerId(Long sqlManagerId){
        this.sqlManager = CacheSqlManagerUtil.getCache(String.valueOf(sqlManagerId), SQLManager.class);
        return this;
    }
    public BeetlSqlUtil defaultSQLManager(){ this.sqlManager = getDefaultSQLManager(); return this; }
    public BeetlSqlUtil sqlManager(SQLManager sqlManager){ this.sqlManager = sqlManager; return this; }
    public BeetlSqlUtil tableName(String tableName){ this.tableName = tableName; return this; }
    public BeetlSqlUtil tableName(Class<?> clazz){
        this.clazz = clazz;
        this.tableName = getTableName(clazz);
        return this;
    }
    public BeetlSqlUtil sqlFile(String sqlFile){ this.sqlFile = sqlFile; return this; }
    public BeetlSqlUtil ivyDbSql(IvyDbSqlVo ivyDbSql){
        sqlManagerId(ivyDbSql.getSqlManagerId());
        tableName(ivyDbSql.getTableName());
        this.ivyDbSql = ivyDbSql;
        return this;
    }

    // 得到数据库的所有表
    public Set<String> allTable(){
        return sqlManager.getMetaDataManager().allTable();
    }

    //执行数据脚本
    public void executeSqlScript(){
        DBInitHelper.executeSqlScript(sqlManager,sqlFile);
    }

    public String getTableName(Class<?> clazz){
        return sqlManager.getNc().getTableName(clazz);
    }

    public String getClassName(String tableName){
        return sqlManager.getNc().getClassName(tableName);
    }

    public String getColName(String propName,Class<?> clazz){
        if(clazz == null){
            return sqlManager.getNc().getColName(propName);
        }
        return sqlManager.getNc().getColName(clazz, propName);
    }

    public List<SelectOptionItem> getColNames() {
        Class<? extends BaseEntity> aClass = SqlUtil.dynamicEntity(sqlManager, tableName);
        Field[] fields = ClassUtil.getDeclaredFields(aClass);
        List<SelectOptionItem> list = new ArrayList<>();
        for (Field field : fields){
            String colName = getColName(field.getName(), aClass);
            list.add(new SelectOptionItem(field.getName()+"【"+colName+"】",colName));
        }
        return list;
    }

    public String getPropName(String colName,Class<?> clazz){
        if(clazz == null) {
            return sqlManager.getNc().getPropertyName(colName);
        }
        return sqlManager.getNc().getPropertyName(clazz, colName);
    }

    public String buildSql() throws SQLBuildException {
        List<IvyDbSqlItem> list = new ArrayList<>();
        list.addAll(ivyDbSql.getWhereList());
        list.addAll(ivyDbSql.getGroupByList());
        list.addAll(ivyDbSql.getOrderByList());
        list = list.stream().filter(m-> StrUtil.isNotBlank(m.getColumn()) && StrUtil.isNotBlank(m.getType())).collect(Collectors.toList());
        list.addAll(ivyDbSql.getLimitList().stream().filter(m-> StrUtil.isNotBlank(m.getType())).collect(Collectors.toList()));
        if(CollUtil.isNotEmpty(list)){
            Interceptor[] inters = sqlManager.getInters();
            sqlManager.setInters(new Interceptor[]{});
            LambdaQuery<? extends BaseEntity> query = SqlUtil.dynamicLambdaQuery(sqlManager, tableName);
            for (IvyDbSqlItem item : list){
                String column = item.getColumn();
                switch (item.getType()){
                    case "andEq": query.andEq(column, "");break;
                    case "andNotEq": query.andNotEq(column, "");break;
                    case "andLike": query.andLike(column, "");break;
                    case "andNotLike": query.andNotLike(column, "");break;
                    case "andIn": query.andIn(column, CollUtil.newHashSet(""));break;
                    case "andNotIn": query.andNotIn(column, CollUtil.newHashSet(""));break;
                    case "andBetween": query.andBetween(column, "","");break;
                    case "andNotBetween": query.andNotBetween(column, "","");break;
                    case "andIsNull": query.andIsNull(column);break;
                    case "andIsNotNull": query.andIsNotNull(column);break;
                    case "andGreat": query.andGreat(column,"");break;
                    case "andGreatEq": query.andGreatEq(column,"");break;
                    case "andLess": query.andLess(column,"");break;
                    case "andLessEq": query.andLessEq(column,"");break;

                    case "orEq": query.orEq(column, "");break;
                    case "orNotEq": query.orNotEq(column, "");break;
                    case "orLike": query.orLike(column, "");break;
                    case "orNotLike": query.orNotLike(column, "");break;
                    case "orIn": query.orIn(column, CollUtil.newHashSet(""));break;
                    case "orNotIn": query.orNotIn(column, CollUtil.newHashSet(""));break;
                    case "orBetween": query.orBetween(column, "","");break;
                    case "orNotBetween": query.orNotBetween(column, "","");break;
                    case "orIsNull": query.orIsNull(column);break;
                    case "orIsNotNull": query.orIsNotNull(column);break;
                    case "orGreat": query.orGreat(column,"");break;
                    case "orGreatEq": query.orGreatEq(column,"");break;
                    case "orLess": query.orLess(column,"");break;
                    case "orLessEq": query.orLessEq(column,"");break;

                    case "asc": query.asc(column);break;
                    case "desc": query.desc(column);break;
                    case "orderBy": query.orderBy(column);break;

                    case "limit": query.limit(item.getStartRow(),item.getPageSize());break;
                    case "groupBy": query.groupBy(column);break;
                    //case "page": query.page(column);break;
                    //case "having": query.having(column);break;


                }
            }
            List<IvyDbSqlItem> columnList = ivyDbSql.getColumnList();
            try {
                if(CollUtil.isNotEmpty(columnList)){
                    List<String> columns = new ArrayList<>();
                    for (IvyDbSqlItem item : columnList){
                        String column = item.getColumn();
                        switch (item.getType()){
                            case "column": columns.add(column);break;
                            case "distinct": columns.add("distinct "+column);break;
                            case "count": columns.add("count("+column+") as "+column);break;
                            case "max": columns.add("max("+column+") as "+column);break;
                            case "min": columns.add("min("+column+") as "+column);break;
                        }
                    }
                    query.select(columns.toArray(new String[0]));
                }else{
                    query.select();
                }
            }catch (Exception e){
                throw new SQLBuildException(e.getMessage());
            }

            IvyParaExtend paraExtend = (IvyParaExtend)sqlManager.getSqlManagerExtend().getParaExtend();
            ExecuteContext ctx = paraExtend.getCtx();
            sqlManager.setInters(inters);
            String sqlTemplate = ctx.sqlSource.template;
            ivyDbSql.setSqlTemplate(sqlTemplate);
            if(ivyDbSql.getFormatSql()){
                return cn.hutool.db.sql.SqlUtil.formatSql(sqlTemplate);
            }
            return sqlTemplate;
        }
        return "";
    }

    public <T> LambdaQuery<T> lambdaQuery(){
        return (LambdaQuery<T>) sqlManager.lambdaQuery(clazz);
    }
}
